﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class HoaDonDAO
    {
        SqlConnection conn = null;

        public List<HoaDonDTO> LayDanhSachHoaDon()
        {
            List<HoaDonDTO> listHD = new List<HoaDonDTO>();
            SqlDataReader reader = null;
            try
            {
                conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_LayDanhSachHoaDon";
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    HoaDonDTO hdDTO = new HoaDonDTO();
                    hdDTO.MaHoaDon = reader.GetInt32(0);
                    hdDTO.MaKhachHang = reader.GetInt32(1);
                    hdDTO.NgayLap = reader.GetDateTime(2);
                    hdDTO.NhanVienLap = reader.GetInt32(3);
                    hdDTO.TongTien = reader.GetDouble(4);
                    hdDTO.GhiChu = reader.GetString(5);
                    listHD.Add(hdDTO);
                }
            }
            catch (Exception ex)
            { String e = ex.ToString(); }
            finally
            { conn.Close(); }
            return listHD;
        }

        public int ThemHoaDon(HoaDonDTO hdDTO)
        {
            int ID = -1;
            try
            {
                conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_ThemHoaDon";
                SqlParameter para;
                para = cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                para.Value = hdDTO.MaKhachHang;
                para = cmd.Parameters.Add("@NgayLap", SqlDbType.DateTime);
                para.Value = hdDTO.NgayLap;
                para = cmd.Parameters.Add("@NhanVienLap", SqlDbType.Int);
                para.Value = hdDTO.NhanVienLap;
                para = cmd.Parameters.Add("@TongTien", SqlDbType.Float);
                para.Value = hdDTO.TongTien;
                para = cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar);
                para.Value = hdDTO.GhiChu;
                cmd.ExecuteNonQuery();

                //Tra ve ma hoa don de luu chi tiet hoa don vao csdl
                String strSQL = "Select @@IDENTITY";
                cmd = new SqlCommand(strSQL, conn);
                ID = Convert.ToInt32(cmd.ExecuteScalar());

                conn.Close();
            }
            catch (Exception ex)
            {
                String e = ex.ToString();
            }
            return ID;
        }

        public string CapNhatHoaDon(HoaDonDTO hdDTO)
        {
            string msg = "";
            try
            {
                conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_CapNhatHoaDon";
                SqlParameter para;
                para = cmd.Parameters.Add("@MaHoaDon", SqlDbType.Int);
                para.Value = hdDTO.MaHoaDon;
                para = cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                para.Value = hdDTO.MaKhachHang;
                para = cmd.Parameters.Add("@NgayLap", SqlDbType.DateTime);
                para.Value = hdDTO.NgayLap;
                para = cmd.Parameters.Add("@NhanVienLap", SqlDbType.Int);
                para.Value = hdDTO.NhanVienLap;
                para = cmd.Parameters.Add("@TongTien", SqlDbType.Float);
                para.Value = hdDTO.TongTien;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                msg = ex.Message;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return msg;
        }

        public string XoaHoaDon(int ma)
        {
            string msg = "";
            try
            {
                conn = DataProvider.ConnectDB();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_XoaHoaDon";
                SqlParameter para;
                para = cmd.Parameters.Add("@MaHoaDon", SqlDbType.Int);
                para.Value = ma;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                msg = ex.Message;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return msg;
        }

        public HoaDonDTO LayThongTinHoaDon(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayThongTinHoaDon";
            cm.Parameters.Add("@MaHoaDon", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            HoaDonDTO dto = new HoaDonDTO();
            dr.Read();
            dto.MaHoaDon = dr.GetInt32(0);
            dto.MaKhachHang = dr.GetInt32(1);
            dto.NgayLap = dr.GetDateTime(2);
            dto.NhanVienLap = dr.GetInt32(3);
            dto.TongTien = dr.GetDouble(4);
            dto.GhiChu = dr.GetString(5);
            dr.Close();
            conn.Close();
            return dto;
        }

        public List<HoaDonDTO> LayDanhSachHoaDonTheoNgay(string tungay, string denngay)
        {
            List<HoaDonDTO> ds = new List<HoaDonDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachHoaDonTheoNgay";
            //cm.Parameters.Add("@TuNgay", SqlDbType.DateTime).Value = tungay;
            //cm.Parameters.Add("@DenNgay", SqlDbType.DateTime).Value = denngay;
            cm.Parameters.Add("@TuNgay", SqlDbType.VarChar).Value = tungay;
            cm.Parameters.Add("@DenNgay", SqlDbType.VarChar).Value = denngay;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                HoaDonDTO dto = new HoaDonDTO();
                dto.MaHoaDon = dr.GetInt32(0);
                dto.MaKhachHang = dr.GetInt32(1);
                dto.NgayLap = dr.GetDateTime(2);
                dto.NhanVienLap = dr.GetInt32(3);
                dto.TongTien = dr.GetDouble(4);
                dto.GhiChu = dr.GetString(5);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }
        //public float TongTienTatCaHoaDon()
        //{
        //    SqlConnection conn = DataProvider.ConnectDB();
        //    SqlCommand cm = new SqlCommand();
        //    cm.Connection = conn;
        //    cm.CommandType = CommandType.StoredProcedure;
        //    cm.CommandText = "usp_TongTienTatCaHoaDon";
        //    float t = (float)cm.ExecuteScalar();
        //    return t;
        //}
    }
}
